package com.weijian.service.impl;

import com.weijian.dal.enums.ChartType;
import com.weijian.dal.enums.DataType;
import com.weijian.dto.data.ChartColumn;
import com.weijian.dto.data.ChartData;
import com.weijian.dto.data.QueryResult;
import com.weijian.service.ChartRecommendationService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.sql.Date;
import java.sql.Timestamp;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 图表推荐服务实现类
 * 分析查询结果的数据特征，智能推荐最合适的图表类型
 *
 * @author miaoma
 */
@Slf4j
@Service
public class ChartRecommendationServiceImpl implements ChartRecommendationService {

    @Override
    public ChartData buildChartData(QueryResult queryResult, List<ChartType> aiRecommendedCharts) {
        if (queryResult == null || queryResult.getRows() == null || queryResult.getRows().isEmpty()) {
            log.warn("Query result is empty, returning default table chart");
            return ChartData.builder()
                    .recommendedChart(ChartType.TABLE)
                    .alternativeCharts(Collections.singletonList(ChartType.TABLE))
                    .columns(Collections.emptyList())
                    .rows(Collections.emptyList())
                    .rowCount(0)
                    .build();
        }

        // 分析列类型
        List<ChartColumn> columns = analyzeColumns(queryResult);

        // 决定推荐的图表类型和备选列表
        ChartType recommendedChart;
        List<ChartType> alternativeCharts;

        if (aiRecommendedCharts != null && !aiRecommendedCharts.isEmpty()) {
            // 使用AI推荐的图表类型
            log.info("Using AI recommended charts: {}", aiRecommendedCharts);
            recommendedChart = aiRecommendedCharts.get(0);
            alternativeCharts = new ArrayList<>(aiRecommendedCharts);

            // 确保TABLE总是在备选列表中
            if (!alternativeCharts.contains(ChartType.TABLE)) {
                alternativeCharts.add(ChartType.TABLE);
            }
        } else {
            // 使用后端规则推荐图表类型
            log.info("Using backend rule-based chart recommendation");
            recommendedChart = recommendChartType(columns, queryResult.getRowCount());
            alternativeCharts = getAlternativeCharts(recommendedChart, columns);
        }

        return ChartData.builder()
                .recommendedChart(recommendedChart)
                .alternativeCharts(alternativeCharts)
                .columns(columns)
                .rows(queryResult.getRows())
                .rowCount(queryResult.getRowCount())
                .build();
    }

    @Override
    public ChartType recommendChartType(List<ChartColumn> columns, int rowCount) {
        long numericCount = columns.stream().filter(ChartColumn::isNumeric).count();
        long categoryCount = columns.stream().filter(ChartColumn::isCategory).count();
        boolean hasTimeSeries = columns.stream().anyMatch(ChartColumn::isTimeSeries);

        log.debug("Chart recommendation analysis - columns: {}, numeric: {}, category: {}, timeSeries: {}, rows: {}",
                columns.size(), numericCount, categoryCount, hasTimeSeries, rowCount);

        // 规则1：时间序列数据 -> 折线图
        if (hasTimeSeries && numericCount >= 1) {
            log.info("Recommending LINE chart for time series data");
            return ChartType.LINE;
        }

        // 规则2：单列数值聚合（如COUNT, SUM） -> 仪表盘
        if (columns.size() == 1 && numericCount == 1 && rowCount == 1) {
            log.info("Recommending GAUGE chart for single numeric aggregate");
            return ChartType.GAUGE;
        }

        // 规则3：一个类别列 + 一个数值列 -> 饼图或柱状图
        if (categoryCount == 1 && numericCount == 1) {
            ChartType chart = rowCount <= 6 ? ChartType.PIE : ChartType.BAR;
            log.info("Recommending {} chart for category + numeric data (rows: {})", chart, rowCount);
            return chart;
        }

        // 规则4：两个数值列 -> 散点图
        if (numericCount == 2 && categoryCount == 0) {
            log.info("Recommending SCATTER chart for two numeric columns");
            return ChartType.SCATTER;
        }

        // 规则5：多个类别列 + 一个数值列 -> 柱状图
        if (categoryCount >= 1 && numericCount >= 1) {
            log.info("Recommending BAR chart for multi-dimension data");
            return ChartType.BAR;
        }

        // 默认：表格
        log.info("Recommending TABLE chart as default");
        return ChartType.TABLE;
    }

    /**
     * 分析所有列的数据类型和特征
     */
    private List<ChartColumn> analyzeColumns(QueryResult queryResult) {
        List<ChartColumn> chartColumns = new ArrayList<>();

        for (String columnName : queryResult.getColumns()) {
            // 提取该列的所有值
            List<Object> columnValues = queryResult.getRows().stream()
                    .map(row -> row.get(columnName))
                    .filter(Objects::nonNull)
                    .collect(Collectors.toList());

            ChartColumn column = analyzeColumn(columnName, columnValues);
            chartColumns.add(column);
        }

        return chartColumns;
    }

    /**
     * 分析单个列的数据类型和特征
     */
    private ChartColumn analyzeColumn(String columnName, List<Object> columnValues) {
        log.info("Analyzing column: {}, values count: {}", columnName, columnValues.size());

        if (columnValues.isEmpty()) {
            log.warn("Column {} has no values, defaulting to STRING/category", columnName);
            return ChartColumn.builder()
                    .name(columnName)
                    .label(columnName)
                    .dataType(DataType.STRING)
                    .isNumeric(false)
                    .isCategory(true)
                    .isTimeSeries(false)
                    .build();
        }

        // 取第一个非null值作为类型判断依据
        Object sampleValue = columnValues.get(0);
        log.info("Column {} sample value: {} (type: {})", columnName, sampleValue,
                sampleValue != null ? sampleValue.getClass().getName() : "null");

        DataType dataType = detectDataType(sampleValue);
        boolean isTimeSeries = isTimeSeriesType(dataType);

        // 智能判断是类别列还是数值列
        // 即使数据类型是NUMBER，某些列也应该作为类别使用
        boolean isCategory = shouldTreatAsCategory(columnName, columnValues, dataType);
        boolean isNumeric = isNumericType(dataType) && !isCategory;

        log.info("Column {} analysis result: dataType={}, isNumeric={}, isCategory={}, isTimeSeries={}",
                columnName, dataType, isNumeric, isCategory, isTimeSeries);

        return ChartColumn.builder()
                .name(columnName)
                .label(formatLabel(columnName))
                .dataType(dataType)
                .isNumeric(isNumeric)
                .isCategory(isCategory)
                .isTimeSeries(isTimeSeries)
                .build();
    }

    /**
     * 判断是否应该作为类别列处理
     * 即使数据类型是数字，某些情况下也应该作为类别（维度）而不是度量（数值）
     */
    private boolean shouldTreatAsCategory(String columnName, List<Object> columnValues, DataType dataType) {
        // 非数值类型，直接作为类别
        if (!isNumericType(dataType)) {
            return true;
        }

        String lowerName = columnName.toLowerCase();

        // 优先判断：列名包含度量关键字，强制作为数值列
        if (lowerName.contains("amount") || lowerName.contains("price") || lowerName.contains("cost") ||
                lowerName.contains("total") || lowerName.contains("sum") || lowerName.contains("count") ||
                lowerName.contains("avg") || lowerName.contains("average") || lowerName.contains("value") ||
                lowerName.contains("revenue") || lowerName.contains("sales") || lowerName.contains("profit") ||
                lowerName.contains("quantity") || lowerName.contains("number") || lowerName.contains("score")) {
            log.info("Column {} treated as numeric due to measure keyword in name", columnName);
            return false;
        }

        // 列名包含维度关键字，作为类别列
        if (lowerName.contains("id") || lowerName.contains("code") || lowerName.contains("type") ||
                lowerName.contains("status") || lowerName.contains("level") || lowerName.contains("grade") ||
                lowerName.contains("year") || lowerName.contains("month") || lowerName.contains("day") ||
                lowerName.contains("category") || lowerName.contains("group") || lowerName.contains("name")) {
            log.info("Column {} treated as category due to dimension keyword in name", columnName);
            return true;
        }

        // 如果数值类型的列只有少量不同值（< 15个），可能是类别列
        // 但如果列名不像维度，即使基数低也保持为数值
        long distinctCount = columnValues.stream().distinct().count();
        if (distinctCount < 15) {
            log.info("Column {} treated as category due to low cardinality (distinct: {})", columnName, distinctCount);
            return true;
        }

        // 默认作为数值列
        log.info("Column {} treated as numeric (default for NUMBER type)", columnName);
        return false;
    }

    /**
     * 检测数据类型
     */
    private DataType detectDataType(Object value) {
        if (value instanceof Number || value instanceof BigDecimal) {
            return DataType.NUMBER;
        } else if (value instanceof Date || value instanceof LocalDate) {
            return DataType.DATE;
        } else if (value instanceof Timestamp || value instanceof LocalDateTime) {
            return DataType.DATETIME;
        } else if (value instanceof Boolean) {
            return DataType.BOOLEAN;
        } else {
            return DataType.STRING;
        }
    }

    /**
     * 判断是否为数值类型
     */
    private boolean isNumericType(DataType dataType) {
        return dataType == DataType.NUMBER;
    }

    /**
     * 判断是否为时间序列类型
     */
    private boolean isTimeSeriesType(DataType dataType) {
        return dataType == DataType.DATE || dataType == DataType.DATETIME;
    }

    /**
     * 格式化列名为友好的显示标签
     */
    private String formatLabel(String columnName) {
        // 将下划线转换为空格，首字母大写
        return Arrays.stream(columnName.split("_"))
                .map(word -> word.substring(0, 1).toUpperCase() + word.substring(1).toLowerCase())
                .collect(Collectors.joining(" "));
    }

    /**
     * 获取备选图表类型
     */
    private List<ChartType> getAlternativeCharts(ChartType primary, List<ChartColumn> columns) {
        List<ChartType> alternatives = new ArrayList<>();
        alternatives.add(primary);

        long numericCount = columns.stream().filter(ChartColumn::isNumeric).count();
        long categoryCount = columns.stream().filter(ChartColumn::isCategory).count();
        boolean hasTimeSeries = columns.stream().anyMatch(ChartColumn::isTimeSeries);

        // 总是提供表格作为备选
        if (primary != ChartType.TABLE) {
            alternatives.add(ChartType.TABLE);
        }

        // 基于数据特征添加其他备选图表
        if (hasTimeSeries && numericCount >= 1) {
            // 时间序列数据可选折线图和面积图
            if (primary != ChartType.LINE) alternatives.add(ChartType.LINE);
            if (primary != ChartType.AREA) alternatives.add(ChartType.AREA);
        }

        if (categoryCount >= 1 && numericCount >= 1) {
            // 分类数据可选柱状图和饼图
            if (primary != ChartType.BAR) alternatives.add(ChartType.BAR);
            if (primary != ChartType.PIE && categoryCount == 1) alternatives.add(ChartType.PIE);
        }

        if (numericCount == 2) {
            // 双数值列可选散点图
            if (primary != ChartType.SCATTER) alternatives.add(ChartType.SCATTER);
        }

        // 限制备选项数量为3-4个
        return alternatives.stream().distinct().limit(4).collect(Collectors.toList());
    }
}
